package edu.sdjzu.exam.service.exam.impl;

import edu.sdjzu.exam.dto.GradeExportDTO;
import edu.sdjzu.exam.mapper.exam.ExamAnswersMapper;
import edu.sdjzu.exam.mapper.exam.StudentGradesMapper;
import edu.sdjzu.exam.service.exam.GradeExportService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.stereotype.Service;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

@Slf4j
@Service
@RequiredArgsConstructor
public class GradeExportServiceImpl implements GradeExportService {

    private final StudentGradesMapper studentGradesMapper;
    private final ExamAnswersMapper examAnswersMapper;

    @Override
    public List<GradeExportDTO.GradeExportData> getExamGradeData(Long examId) {
        log.info("获取考试 {} 的成绩数据", examId);

        // 从数据库获取成绩数据（包含学生信息、教师信息）
        List<Map<String, Object>> gradeData = studentGradesMapper.selectDetailedGradesByExamId(examId);

        // 转换为DTO并排序
        List<GradeExportDTO.GradeExportData> exportData = gradeData.stream()
                .map(this::convertToGradeExportData)
                .sorted((a, b) -> {
                    // 按总分降序排序
                    if (a.getTotalScore() == null && b.getTotalScore() == null) return 0;
                    if (a.getTotalScore() == null) return 1;
                    if (b.getTotalScore() == null) return -1;
                    return b.getTotalScore().compareTo(a.getTotalScore());
                })
                .collect(Collectors.toList());

        // 计算排名
        calculateRanking(exportData);

        return exportData;
    }

    @Override
    public GradeExportDTO.GradeStatistics getExamGradeStatistics(Long examId) {
        log.info("计算考试 {} 的成绩统计", examId);

        List<GradeExportDTO.GradeExportData> gradeData = getExamGradeData(examId);
        
        GradeExportDTO.GradeStatistics statistics = new GradeExportDTO.GradeStatistics();
        statistics.setTotalStudents(gradeData.size());

        List<GradeExportDTO.GradeExportData> completedGrades = gradeData.stream()
                .filter(grade -> grade.getTotalScore() != null)
                .collect(Collectors.toList());

        statistics.setCompletedStudents(completedGrades.size());

        if (!completedGrades.isEmpty()) {
            // 计算平均分
            BigDecimal avgScore = completedGrades.stream()
                    .map(GradeExportDTO.GradeExportData::getTotalScore)
                    .reduce(BigDecimal.ZERO, BigDecimal::add)
                    .divide(BigDecimal.valueOf(completedGrades.size()), 2, RoundingMode.HALF_UP);
            statistics.setAvgScore(avgScore);

            // 最高分和最低分
            statistics.setMaxScore(completedGrades.stream()
                    .map(GradeExportDTO.GradeExportData::getTotalScore)
                    .max(BigDecimal::compareTo)
                    .orElse(BigDecimal.ZERO));

            statistics.setMinScore(completedGrades.stream()
                    .map(GradeExportDTO.GradeExportData::getTotalScore)
                    .min(BigDecimal::compareTo)
                    .orElse(BigDecimal.ZERO));

            // 假设满分是100，计算及格率和优秀率
            BigDecimal fullScore = completedGrades.get(0).getMaxScore();
            if (fullScore != null && fullScore.compareTo(BigDecimal.ZERO) > 0) {
                long passCount = completedGrades.stream()
                        .mapToLong(grade -> {
                            BigDecimal percentage = grade.getTotalScore()
                                    .divide(fullScore, 4, RoundingMode.HALF_UP)
                                    .multiply(BigDecimal.valueOf(100));
                            return percentage.compareTo(BigDecimal.valueOf(60)) >= 0 ? 1 : 0;
                        })
                        .sum();

                long excellentCount = completedGrades.stream()
                        .mapToLong(grade -> {
                            BigDecimal percentage = grade.getTotalScore()
                                    .divide(fullScore, 4, RoundingMode.HALF_UP)
                                    .multiply(BigDecimal.valueOf(100));
                            return percentage.compareTo(BigDecimal.valueOf(85)) >= 0 ? 1 : 0;
                        })
                        .sum();

                statistics.setPassRate(BigDecimal.valueOf(passCount)
                        .divide(BigDecimal.valueOf(completedGrades.size()), 4, RoundingMode.HALF_UP)
                        .multiply(BigDecimal.valueOf(100)));

                statistics.setExcellentRate(BigDecimal.valueOf(excellentCount)
                        .divide(BigDecimal.valueOf(completedGrades.size()), 4, RoundingMode.HALF_UP)
                        .multiply(BigDecimal.valueOf(100)));
            }
        }

        return statistics;
    }

    @Override
    public GradeExportDTO.ExamGradeReport generateExamGradeReport(Long examId) {
        log.info("生成考试 {} 的成绩报告", examId);

        // 获取考试基本信息
        Map<String, Object> examInfo = studentGradesMapper.selectExamBasicInfo(examId);
        if (examInfo == null) {
            throw new RuntimeException("考试不存在，ID: " + examId);
        }

        GradeExportDTO.ExamGradeReport report = new GradeExportDTO.ExamGradeReport();
        report.setExamId(examId);
        report.setExamTitle((String) examInfo.get("title"));
        report.setExamDate((LocalDateTime) examInfo.get("start_time"));
        report.setStatistics(getExamGradeStatistics(examId));
        report.setGrades(getExamGradeData(examId));

        return report;
    }

    @Override
    public ByteArrayResource exportGradesToExcel(Long examId) throws IOException {
        log.info("导出考试 {} 的Excel成绩报告", examId);

        GradeExportDTO.ExamGradeReport report = generateExamGradeReport(examId);

        try (Workbook workbook = new XSSFWorkbook();
             ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {

            // 创建样式
            CellStyle headerStyle = createHeaderStyle(workbook);
            CellStyle dataStyle = createDataStyle(workbook);

            // 创建成绩明细表
            createGradeDetailSheet(workbook, report, headerStyle, dataStyle);

            // 创建统计信息表
            createStatisticsSheet(workbook, report, headerStyle, dataStyle);

            workbook.write(outputStream);
            return new ByteArrayResource(outputStream.toByteArray());
        }
    }

    @Override
    public ByteArrayResource exportGradesToCsv(Long examId) throws IOException {
        log.info("导出考试 {} 的CSV成绩报告", examId);

        GradeExportDTO.ExamGradeReport report = generateExamGradeReport(examId);

        StringBuilder csv = new StringBuilder();
        
        // CSV头部
        csv.append("学号,姓名,总分,满分,百分比,状态,排名,批改时间,批改教师\n");
        
        // 数据行
        for (GradeExportDTO.GradeExportData grade : report.getGrades()) {
            csv.append(String.format("%s,%s,%s,%s,%s,%s,%s,%s,%s\n",
                    csvEscape(grade.getStudentId()),
                    csvEscape(grade.getStudentName()),
                    grade.getTotalScore() != null ? grade.getTotalScore().toString() : "",
                    grade.getMaxScore() != null ? grade.getMaxScore().toString() : "",
                    grade.getPercentage() != null ? grade.getPercentage().toString() + "%" : "",
                    csvEscape(grade.getGradeStatus()),
                    csvEscape(grade.getRank()),
                    grade.getGradedAt() != null ? grade.getGradedAt().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) : "",
                    csvEscape(grade.getGradedByName())
            ));
        }

        return new ByteArrayResource(csv.toString().getBytes("UTF-8"));
    }

    private GradeExportDTO.GradeExportData convertToGradeExportData(Map<String, Object> row) {
        GradeExportDTO.GradeExportData data = new GradeExportDTO.GradeExportData();
        
        // 安全地获取字段值，添加空值检查
        Object studentId = row.get("student_id");
        data.setStudentId(studentId != null ? String.valueOf(studentId) : "");
        data.setStudentName((String) row.get("student_name"));
        data.setExamTitle((String) row.get("exam_title"));
        data.setTotalScore((BigDecimal) row.get("total_score"));
        data.setGradeStatus((String) row.get("grade_status"));
        data.setGradedAt((LocalDateTime) row.get("graded_at"));

        // 从exam_answers表获取满分
        Object examIdObj = row.get("exam_id");
        if (examIdObj != null) {
            BigDecimal maxScore = examAnswersMapper.getMaxScoreByExam(Long.valueOf(examIdObj.toString()));
            data.setMaxScore(maxScore);

            // 计算百分比
            if (data.getTotalScore() != null && maxScore != null && 
                maxScore.compareTo(BigDecimal.ZERO) > 0) {
                BigDecimal percentage = data.getTotalScore()
                        .divide(maxScore, 4, RoundingMode.HALF_UP)
                        .multiply(BigDecimal.valueOf(100));
                data.setPercentage(percentage);
            }
        }

        return data;
    }

    private void calculateRanking(List<GradeExportDTO.GradeExportData> gradeData) {
        int rank = 1;
        BigDecimal previousScore = null;
        int sameRankCount = 0;

        for (int i = 0; i < gradeData.size(); i++) {
            GradeExportDTO.GradeExportData grade = gradeData.get(i);
            BigDecimal currentScore = grade.getTotalScore();

            if (currentScore == null) {
                grade.setRank("-");
            } else {
                if (previousScore != null && currentScore.compareTo(previousScore) == 0) {
                    // 同分，使用相同排名
                    grade.setRank(String.valueOf(rank));
                    sameRankCount++;
                } else {
                    // 不同分，更新排名为当前位置+1
                    rank = i + 1;  // 关键修复：使用当前索引位置+1作为新排名
                    grade.setRank(String.valueOf(rank));
                    sameRankCount = 1;
                }
                previousScore = currentScore;
            }
        }
    }

    private CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        return style;
    }

    private CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        return style;
    }

    private void createGradeDetailSheet(Workbook workbook, GradeExportDTO.ExamGradeReport report, 
                                       CellStyle headerStyle, CellStyle dataStyle) {
        Sheet sheet = workbook.createSheet("成绩明细");

        // 创建标题行
        Row titleRow = sheet.createRow(0);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(report.getExamTitle() + " - 成绩明细");

        // 创建表头
        Row headerRow = sheet.createRow(2);
        String[] headers = {"学号", "姓名", "总分", "满分", "百分比", "排名", "状态", "批改时间", "批改教师"};
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }

        // 填充数据
        int rowNum = 3;
        for (GradeExportDTO.GradeExportData grade : report.getGrades()) {
            Row row = sheet.createRow(rowNum++);
            
            // 确保所有列都创建Cell，避免空指针异常
            row.createCell(0).setCellValue(grade.getStudentId() != null ? grade.getStudentId() : "");
            row.createCell(1).setCellValue(grade.getStudentName() != null ? grade.getStudentName() : "");
            
            if (grade.getTotalScore() != null) {
                row.createCell(2).setCellValue(grade.getTotalScore().doubleValue());
            } else {
                row.createCell(2).setCellValue("");
            }
            
            if (grade.getMaxScore() != null) {
                row.createCell(3).setCellValue(grade.getMaxScore().doubleValue());
            } else {
                row.createCell(3).setCellValue("");
            }
            
            if (grade.getPercentage() != null) {
                row.createCell(4).setCellValue(grade.getPercentage().doubleValue() + "%");
            } else {
                row.createCell(4).setCellValue("");
            }
            
            row.createCell(5).setCellValue(grade.getRank());
            row.createCell(6).setCellValue(grade.getGradeStatus() != null ? grade.getGradeStatus() : "");
            
            if (grade.getGradedAt() != null) {
                row.createCell(7).setCellValue(grade.getGradedAt().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
            } else {
                row.createCell(7).setCellValue("");
            }
            
            if (grade.getGradedByName() != null) {
                row.createCell(8).setCellValue(grade.getGradedByName());
            } else {
                row.createCell(8).setCellValue("");
            }

            // 应用样式
            for (int i = 0; i < headers.length; i++) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    cell.setCellStyle(dataStyle);
                }
            }
        }

        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
    }

    private void createStatisticsSheet(Workbook workbook, GradeExportDTO.ExamGradeReport report, 
                                      CellStyle headerStyle, CellStyle dataStyle) {
        Sheet sheet = workbook.createSheet("统计信息");

        int rowNum = 0;
        
        // 考试信息
        sheet.createRow(rowNum++).createCell(0).setCellValue("考试名称：" + report.getExamTitle());
        if (report.getExamDate() != null) {
            sheet.createRow(rowNum++).createCell(0).setCellValue("考试时间：" + 
                    report.getExamDate().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
        }
        rowNum++; // 空行

        // 统计信息
        GradeExportDTO.GradeStatistics stats = report.getStatistics();
        sheet.createRow(rowNum++).createCell(0).setCellValue("参考人数：" + stats.getTotalStudents());
        sheet.createRow(rowNum++).createCell(0).setCellValue("已批改人数：" + stats.getCompletedStudents());
        
        if (stats.getAvgScore() != null) {
            sheet.createRow(rowNum++).createCell(0).setCellValue("平均分：" + stats.getAvgScore());
        }
        if (stats.getMaxScore() != null) {
            sheet.createRow(rowNum++).createCell(0).setCellValue("最高分：" + stats.getMaxScore());
        }
        if (stats.getMinScore() != null) {
            sheet.createRow(rowNum++).createCell(0).setCellValue("最低分：" + stats.getMinScore());
        }
        if (stats.getPassRate() != null) {
            sheet.createRow(rowNum++).createCell(0).setCellValue("及格率：" + stats.getPassRate() + "%");
        }
        if (stats.getExcellentRate() != null) {
            sheet.createRow(rowNum++).createCell(0).setCellValue("优秀率：" + stats.getExcellentRate() + "%");
        }

        // 自动调整列宽
        sheet.autoSizeColumn(0);
    }

    private String csvEscape(String value) {
        if (value == null) return "";
        // CSV转义：如果包含逗号、双引号或换行符，则用双引号包围，并将双引号转义为两个双引号
        if (value.contains(",") || value.contains("\"") || value.contains("\n")) {
            return "\"" + value.replace("\"", "\"\"") + "\"";
        }
        return value;
    }
}